from flask import Blueprint, request
from common.libs.Helper import ops_renderJSON, ops_renderErrJSON, iPagenation, ops_render
from common.models.user import User
from application import db, app
from common.libs.DataHelper import getCurrentTime, getCurrentDate
from flask import json
import datetime
from sqlalchemy import func
import requests
import urllib.request
import flask_excel as excel
import pandas as pd
from sqlalchemy import create_engine
from dateutil.relativedelta import relativedelta
from echarts.base_wordcloud import base_wordcloud
from echarts.base_pie import base_pie
from echarts.base_bar import base_bar
from datetime import date, timedelta
from application import ExoskeletonEngine as engine

thing_page = Blueprint("thing_page", __name__)

# 获得当前日期和一年前日期
edate = date.today().strftime("%Y-%m-%d")
sdate = (date.today() - timedelta(days=365)).strftime("%Y-%m-%d")

sql1 = "'''" + "select * from thing_order where updated_time > " + "'" + sdate + "'" + ";" + "'''"
sql2 = ''' select * from user; '''
sql = eval(sql1)


sealdata = pd.read_sql_query(sql, engine)
userdata = pd.read_sql_query(sql2, engine).replace(["其他公司", "山西公司", "西北区域公司"], ["", "", ""])

userdata.rename(columns={'id': 'uid'}, inplace=True)
ordata = pd.merge(sealdata, userdata, on='uid')

ordata['year'] = ordata.updated_time_x.dt.year
ordata['month'] = ordata.updated_time_x.dt.month


@thing_page.route("/index")
def thingindex():
    date = datetime.date.today() - relativedelta(months=+1)
    datestr = date.strftime("%Y%m")
    return ops_render("/thing/index.html", {"datestr": datestr, "part": "total"})

@thing_page.route("/total")
def thingtotal():
    date = request.args.get("date")
    return ops_render("/thing/index.html", {"datestr": date, "part": "total"})


@thing_page.route("/api_thing_xh_pie")
def thing_xh_pie():
    date = request.args.get("date")
    years = int(date[:4])
    month = int(date[4:])


    data = ordata[(ordata["year"] == years) & (ordata["month"] == month) & (ordata["mark"] == 2) & (ordata["type"] == "LY")& (ordata["classone"] == "打印机耗材")& (ordata["city_x"] == "太原")].loc[:, [
                                                "classtwo",
                                                "num",

                                            ]]

    data = data.groupby(["classtwo"]).agg(
        {"num": 'sum'}).reset_index()

    x = data["classtwo"].values.tolist()
    y = data['num'].values.tolist()

    showdata = base_pie(x=x, y1=y, title="（单位：个）")
    return showdata.dump_options_with_quotes()


@thing_page.route("/api_thing_ys_pie")
def thing_ys_pie():
    date = request.args.get("date")
    years = int(date[:4])
    month = int(date[4:])


    data = ordata[(ordata["year"] == years) & (ordata["month"] == month) & (ordata["mark"] == 2) & (ordata["type"] == "LY")& (ordata["classone"] == "打印机耗材")& (ordata["city_x"] == "太原")].loc[:, [
                                                "classthree",
                                                "num",

                                            ]]

    data = data.groupby(["classthree"]).agg(
        {"num": 'sum'}).reset_index()

    x = data["classthree"].values.tolist()
    y = data['num'].values.tolist()

    showdata = base_pie(x=x, y1=y, title="（单位：个）")
    return showdata.dump_options_with_quotes()

@thing_page.route("/api_thing_zz_pie")
def thing_zz_pie():
    date = request.args.get("date")
    years = int(date[:4])
    month = int(date[4:])


    data = ordata[(ordata["year"] == years) & (ordata["month"] == month) & (ordata["mark"] == 2) & (ordata["type"] == "LY")& (ordata["classtwo"] == "打印纸")& (ordata["city_x"] == "太原")].loc[:, [
                                                "classthree",
                                                "num",

                                            ]]

    data = data.groupby(["classthree"]).agg(
        {"num": 'sum'}).reset_index()

    x = data["classthree"].values.tolist()
    y = data['num'].values.tolist()

    showdata = base_pie(x=x, y1=y, title="（单位：包）")
    return showdata.dump_options_with_quotes()


@thing_page.route("/api_thing_zz_bar")
def thing_zz_bar():
    date = request.args.get("date")
    years = int(date[:4])
    month = int(date[4:])


    data = ordata[(ordata["year"] == years) & (ordata["month"] == month) & (ordata["mark"] == 2) & (ordata["type"] == "LY")& (ordata["classtwo"] == "打印纸")& (ordata["city_x"] == "太原")].loc[:, [
                                                "department",
                                                "num",

                                            ]]

    data = data.groupby(["department"]).agg(
        {"num": 'sum'}).reset_index()

    x = data["department"].values.tolist()
    y = data['num'].values.tolist()

    legend = "纸张领用数（单位：包）"
    color = "#d87c7c"
    showdata = base_bar(x=x, y1=y, title="", legend=legend, color=color, rotate=30)
    return showdata.dump_options_with_quotes()


@thing_page.route("/api_thing_dy_bar")
def thing_dy_bar():
    date = request.args.get("date")
    years = int(date[:4])
    month = int(date[4:])


    data = ordata[(ordata["year"] == years) & (ordata["month"] == month) & (ordata["mark"] == 2) & (ordata["type"] == "LY")& (ordata["classone"] == "打印机耗材")& (ordata["city_x"] == "太原")].loc[:, [
                                                "department",
                                                "num",

                                            ]]

    data = data.groupby(["department"]).agg(
        {"num": 'sum'}).reset_index()

    x = data["department"].values.tolist()
    y = data['num'].values.tolist()

    legend = "打印机耗材领用数（单位：个）"
    color = "#919e8b"
    showdata = base_bar(x=x, y1=y, title="", legend=legend, color=color, rotate=30)
    return showdata.dump_options_with_quotes()